/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package com.model;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.db.SqlManager;
import com.exception.DataException;
import com.javabeans.Departamento;
import com.javabeans.Facultad;
import com.javabeans.Gestion;
import com.javabeans.TipoGestion;
import com.javabeans.Usuario;
import com.javabeans.VentaMatricula;
/**
 * @author Administrador
 */
public class AdminVentaMatricula implements VentaMatriculaManager{
    private Connection c;
    
    public AdminVentaMatricula(Connection c){
        this.c = c;
    }
    
    @Override
    public int getIdVentaMatricula() throws DataException {
        int res = 0;
        ResultSet rs = null;
        PreparedStatement preSt = null;
        String query = "SELECT IFNULL(max(IDVENTA_MATRICULA)+1,1) AS ID FROM VENTA_MATRICULA";
        try{
            preSt = c.prepareStatement(query);
            rs = preSt.executeQuery();
            rs.next();
            res = rs.getInt("ID");
        }catch(SQLException e){
            throw new DataException(" AdminVentaMatricula -> getIdVentaMatricula() "+e.getMessage());
        }finally{
            SqlManager.closeRs(rs);
            SqlManager.closePreSt(preSt);
        }
        return res;
    }

    @Override
    public void saveVentaMatricula(VentaMatricula vm) throws DataException {
        PreparedStatement preSt = null;
        String query = "";
        try{
            query="INSERT INTO VENTA_MATRICULA VALUES("+vm.getIdVentaMatricula();
            query+= ", '"+vm.getFechaIni();
            query+= "', '"+vm.getFechaFin();
            query+= "', "+vm.getCosto();
            query+= ", "+vm.getIdGestion();
            query+= ", "+vm.getIdUsuario()+","+vm.isActivo()+")";
            preSt = c.prepareStatement(query);
            preSt.executeUpdate();
        }catch(SQLException ex){
            throw new DataException(" AdminVentaMatricula -> saveVentaMatricula(VentaMatricula vm) "+ex.getMessage());
        }finally{
            SqlManager.closePreSt(preSt);
        }
    }
    
    @Override
    public List<VentaMatricula> getVentaMatriculasVigentes() throws DataException{
        List<VentaMatricula> vms = new ArrayList<VentaMatricula>();
        try{
            vms = this.getVentaMatriculaGeneric(0, false);
        }catch(SQLException e){
            throw new DataException(" AdminVentaMatricula -> getVentaMatriculasVigentes() e = "+e.getMessage());
        }
        return vms;
    }
    
    @Override
    public List<VentaMatricula> getVentaMatriculasVigentesPorFacul(int idFacultad) throws DataException{
        List<VentaMatricula> vms = new ArrayList<VentaMatricula>();
        try{
            vms = this.getVentaMatriculaGeneric(idFacultad, false);
        }catch(SQLException e){
            throw new DataException(" AdminVentaMatricula -> getVentaMatriculasVigentesPorFacul(int idFacultad) e = "+e.getMessage());
        }
        return vms;
    }
    
    @Override
    public List<VentaMatricula> getVentaMatriculasPasadas() throws DataException{
        List<VentaMatricula> vms = new ArrayList<VentaMatricula>();
        try{
            vms = this.getVentaMatriculaGeneric(0, true);
        }catch(SQLException e){
            throw new DataException(" AdminVentaMatricula -> getVentaMatriculasPasadas() e = "+e.getMessage());
        }
        return vms;
    }
    
    @Override
    public List<VentaMatricula> getVentaMatriculasPasadasPorFacul(int idFacultad) throws DataException{
        List<VentaMatricula> vms = new ArrayList<VentaMatricula>();
        try{
            vms = this.getVentaMatriculaGeneric(idFacultad, true);
        }catch(SQLException e){
            throw new DataException(" AdminVentaMatricula -> getVentaMatriculasPasadasPorFacul(int idFacultad) e = "+e.getMessage());
        }
        return vms;
    }
    
    public List<VentaMatricula> getVentaMatriculaGeneric(int idFacultad, boolean isGestPasados) throws DataException{
        List<VentaMatricula> vms = new ArrayList<VentaMatricula>();
        ResultSet rs = null;
        PreparedStatement preSt = null;
        try{
            String query = "SELECT VM.IDVENTA_MATRICULA";
            query += ", DATE_FORMAT(VM.FECHAINI,'%d/%m/%Y %H:%i') FECHA_INI";
            query += ", DATE_FORMAT(VM.FECHAFIN,'%d/%m/%Y %H:%i') FECHA_FIN";
            query += ", VM.COSTO, VM.ACTIVO";
            query += ", G.IDGESTION, G.NOMBRE, G.ESTADO";
            query += ", FA.IDFACULTAD, FA.NOMBRE, FA.SIGLA";
            query += ", U.NOMBRE, U.APELLIDO";
            query += ", D.NOMBRE, D.SIGLA";
            query += " FROM VENTA_MATRICULA VM";
            query += " INNER JOIN USUARIO U ON VM.IDUSUARIO=U.IDUSUARIO";
            query += ", GESTION G";
            query += " INNER JOIN (FACULTAD FA INNER JOIN DEPARTAMENTO D ON FA.IDDEPARTAMENTO=D.IDDEPARTAMENTO)";
            query += " ON G.IDFACULTAD=FA.IDFACULTAD";
            query += " WHERE VM.IDGESTION=G.IDGESTION";
            if(idFacultad != 0)
                query += " AND G.IDFACULTAD=1";
            if(isGestPasados)
                query += " AND G.ESTADO = 0";
            else
                query += " AND G.ESTADO > 0";
            // para ordenar
            if(idFacultad != 0)
                query += " ORDER BY G.ESTADO, G.FECHAINI";
            else
                query += " ORDER BY FA.NOMBRE, G.ESTADO, G.FECHAINI";            
            preSt = c.prepareStatement(query);
            rs = preSt.executeQuery();
            while(rs.next()){
                VentaMatricula vm = new VentaMatricula();
                vm.setIdVentaMatricula(rs.getInt("VM.IDVENTA_MATRICULA"));
                vm.setFechaIni(rs.getString("FECHA_INI"));
                vm.setFechaFin(rs.getString("FECHA_FIN"));
                vm.setCosto(rs.getFloat("VM.COSTO"));
                vm.setIdGestion(rs.getInt("G.IDGESTION"));
                vm.setActivo(rs.getBoolean("VM.ACTIVO"));
                
                Gestion g = new Gestion();
                g.setIdGestion(rs.getInt("G.IDGESTION"));
                g.setNombre(rs.getString("G.NOMBRE"));
                g.setEstado(rs.getInt("G.ESTADO"));
                g.setIdFacultad(rs.getInt("FA.IDFACULTAD"));
                
                Facultad f = new Facultad();
                f.setIdFacultad(rs.getInt("FA.IDFACULTAD"));
                f.setNombre(rs.getString("FA.NOMBRE"));
                f.setSigla(rs.getString("FA.SIGLA"));
                
                Departamento depto = new Departamento();
                depto.setNombre(rs.getString("D.NOMBRE"));
                depto.setSigla(rs.getString("D.SIGLA"));
                f.setDepartamento(depto);
                g.setFacultad(f);
                
                Usuario u = new Usuario();
                u.setNombre(rs.getString("U.NOMBRE"));
                u.setApellido(rs.getString("U.APELLIDO"));
                vm.setAdministrativo(u);
                vm.setGestion(g);
                vms.add(vm);
            }
        }catch(SQLException e){
            throw new DataException(" -> getVentaMatriculaGeneric(int idFacultad, int estado, boolean isGestPasados) e = "+e.getMessage());
        }finally{
            SqlManager.closeRs(rs);
            SqlManager.closePreSt(preSt);
        }
        return vms;
    }
    
    @Override
    public void cancelarVentaMatricula(int idVentaMatricula) throws DataException{
        PreparedStatement preSt = null;
        try{
            String query = "UPDATE VENTA_MATRICULA SET ACTIVO=0"; 
            query += " WHERE IDVENTA_MATRICULA="+idVentaMatricula;            
            preSt = c.prepareStatement(query);
            preSt.executeUpdate();
        }catch(SQLException ex){
            throw new DataException(" AdminVentaMatricula -> cancelarVentaMatricula(int idVentaMatricula) e = "+ex.getMessage());
        }finally{
            SqlManager.closePreSt(preSt);
        }
    }
    
    @Override
    public VentaMatricula getVentaMatriculaXfacultad(int idUsuario, String fechaNow) throws DataException {
        VentaMatricula vm = new VentaMatricula();
        ResultSet rs = null;
        PreparedStatement preSt = null;
        try{
            String query = "SELECT F.IDFACULTAD";
                query +=", F.NOMBRE";
                query +=", F.SIGLA";
                query +=", G.IDGESTION";
                query +=", G.NOMBRE";
                query +=", G.YEAR";
                query +=", DATE_FORMAT(G.FECHAINI,'%d/%m/%Y') G_FECHAINI";
                query +=", DATE_FORMAT(G.FECHAFIN,'%d/%m/%Y') G_FECHAFIN";
                query +=", TG.IDTIPO_GESTION";
                query +=", TG.NOMBRE";
                query +=", VM.IDVENTA_MATRICULA";
                query +=", DATE_FORMAT(VM.FECHAINI,'%d/%m/%Y %H:%i:%s') VM_FECHAINI";
                query +=", DATE_FORMAT(VM.FECHAFIN,'%d/%m/%Y %H:%i:%s') VM_FECHAFIN";
                query +=", VM.COSTO";
                query +=", VM.IDGESTION";
                query +=", VM.IDUSUARIO";
                query +=" FROM FACULTAD F";
                query +=" INNER JOIN DETALLE_ADMINISTRATIVO DA ON F.IDFACULTAD=DA.IDFACULTAD";
                query +=" INNER JOIN (GESTION G INNER JOIN VENTA_MATRICULA VM ON G.IDGESTION=VM.IDGESTION) ON F.IDFACULTAD=G.IDFACULTAD ,TIPO_GESTION TG";
                query +=" WHERE G.IDTIPO_GESTION=TG.IDTIPO_GESTION";
                query +=" AND DA.IDDETALLE_ADMINISTRATIVO="+idUsuario;
                query +=" AND G.ESTADO=1";
                query +=" AND '"+fechaNow+"' BETWEEN VM.FECHAINI AND VM.FECHAFIN LIMIT 1";// 2012-07-17 13:32:00           
            preSt = c.prepareStatement(query);
            rs = preSt.executeQuery();
            if(rs.next()){
                Facultad f = new Facultad();
                f.setIdFacultad(rs.getInt("F.IDFACULTAD"));
                f.setNombre(rs.getString("F.NOMBRE"));
                f.setSigla(rs.getString("SIGLA"));
                
                TipoGestion tg = new TipoGestion();
                tg.setIdTipoGestion(rs.getInt("TG.IDTIPO_GESTION"));
                tg.setNombre(rs.getString("TG.NOMBRE"));
                
                Gestion g = new Gestion();
                g.setIdGestion(rs.getInt("G.IDGESTION"));
                g.setNombre(rs.getString("G.NOMBRE"));
                g.setYear(rs.getInt("G.YEAR"));
                g.setFechaIni(rs.getString("G_FECHAINI"));
                g.setFechaFin(rs.getString("G_FECHAFIN"));
                g.setFacultad(f);
                g.setTipoGestion(tg);                
                
                vm.setIdVentaMatricula(rs.getInt("VM.IDVENTA_MATRICULA"));
                vm.setFechaIni(rs.getString("VM_FECHAINI"));
                vm.setFechaFin(rs.getString("VM_FECHAFIN"));
                vm.setCosto(rs.getFloat("COSTO"));
                vm.setIdGestion(rs.getInt("VM.IDGESTION"));
                vm.setIdUsuario(rs.getInt("IDUSUARIO"));
                vm.setGestion(g);
            }
        }catch(SQLException e){
            throw new DataException(" AdminVentaMatricula -> getVentaMatriculaXfacultad(idUsuario,fechaNow) e = "+e.getMessage());
        }finally{
            SqlManager.closeRs(rs);
            SqlManager.closePreSt(preSt);
        }
        return vm;
    }
    
 @Override
 public VentaMatricula getVentaMatriculaXfacultad(int idUsuario, String fechaNow, int idGestion, int idFacultad, boolean flag) throws DataException {
        VentaMatricula v_matriculas = new VentaMatricula();
        ResultSet rs = null;
        PreparedStatement preSt = null;
        try{
            String query = "SELECT F.IDFACULTAD";
                query +=", F.NOMBRE";
                query +=", F.SIGLA";
                query +=", G.IDGESTION";
                query +=", G.NOMBRE";
                query +=", G.YEAR";
                query +=", G.ESTADO";
                query +=", DATE_FORMAT(G.FECHAINI,'%d/%m/%Y') G_FECHAINI";
                query +=", DATE_FORMAT(G.FECHAFIN,'%d/%m/%Y') G_FECHAFIN";
                query +=", TG.IDTIPO_GESTION";
                query +=", TG.NOMBRE";
                query +=", VM.IDVENTA_MATRICULA";
                query +=", DATE_FORMAT(VM.FECHAINI,'%d/%m/%Y %H:%i:%s') VM_FECHAINI";
                query +=", DATE_FORMAT(VM.FECHAFIN,'%d/%m/%Y %H:%i:%s') VM_FECHAFIN";
                query +=", VM.COSTO";
                query +=", VM.IDGESTION";
                query +=", VM.IDUSUARIO";
                query +=" FROM FACULTAD F";
                query +=" INNER JOIN DETALLE_ADMINISTRATIVO DA ON F.IDFACULTAD=DA.IDFACULTAD";
                query +=" INNER JOIN (GESTION G INNER JOIN VENTA_MATRICULA VM ON G.IDGESTION=VM.IDGESTION ) ON F.IDFACULTAD=G.IDFACULTAD ,TIPO_GESTION TG";
                query +=" WHERE G.IDTIPO_GESTION=TG.IDTIPO_GESTION AND VM.IDGESTION="+idGestion+" AND G.IDFACULTAD="+idFacultad;
                if(flag)
                    query +=" AND DA.IDDETALLE_ADMINISTRATIVO="+idUsuario;// este modificar
                query +=" AND '"+fechaNow+"' BETWEEN VM.FECHAINI AND VM.FECHAFIN LIMIT 1";// 2012-07-17 13:32:00
                        
            preSt = c.prepareStatement(query);
            rs = preSt.executeQuery();
            if(rs.next()){
                Facultad f = new Facultad();
                f.setIdFacultad(rs.getInt("F.IDFACULTAD"));
                f.setNombre(rs.getString("F.NOMBRE"));
                f.setSigla(rs.getString("SIGLA"));
                
                TipoGestion tg = new TipoGestion();
                tg.setIdTipoGestion(rs.getInt("TG.IDTIPO_GESTION"));
                tg.setNombre(rs.getString("TG.NOMBRE"));
                
                Gestion g = new Gestion();
                g.setIdGestion(rs.getInt("G.IDGESTION"));
                g.setNombre(rs.getString("G.NOMBRE"));
                g.setYear(rs.getInt("G.YEAR"));
                g.setFechaIni(rs.getString("G_FECHAINI"));
                g.setFechaFin(rs.getString("G_FECHAFIN"));
                g.setEstado(rs.getInt("ESTADO"));
                g.setFacultad(f);
                g.setTipoGestion(tg);                
                
                v_matriculas.setIdVentaMatricula(rs.getInt("VM.IDVENTA_MATRICULA"));
                v_matriculas.setFechaIni(rs.getString("VM_FECHAINI"));
                v_matriculas.setFechaFin(rs.getString("VM_FECHAFIN"));
                v_matriculas.setCosto(rs.getFloat("COSTO"));
                v_matriculas.setIdGestion(rs.getInt("VM.IDGESTION"));
                v_matriculas.setIdUsuario(rs.getInt("IDUSUARIO"));
                v_matriculas.setGestion(g);
            }
        }catch(SQLException e){
            throw new DataException(" AdminVentaMatricula -> getVentaMatriculaXfacultad(idUsuario,fechaNow) e = "+e.getMessage());
        }finally{
            SqlManager.closeRs(rs);
            SqlManager.closePreSt(preSt);
        }
        return v_matriculas;
    }
}
